package com.css.zfzx.fwsjcj.modules.structure.repository.impl;

import com.css.zfzx.fwsjcj.modules.statistics.user.vo.UserVo;
import com.css.zfzx.fwsjcj.modules.structure.repository.FwStructureFormQueryRepository;
import com.css.zfzx.fwsjcj.modules.structure.vo.ExportForBuildingAreaVo;
import com.css.zfzx.fwsjcj.modules.structure.vo.ExportForBuildingAreaWithTaskVo;
import com.css.zfzx.fwsjcj.modules.structure.vo.ExportForBuildingVo;
import com.css.zfzx.fwsjcj.modules.structure.vo.ExportForBuildingWithTaskVo;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * @author DBJ
 */
@Repository
public class FwStructureFormQueryRepositoryImpl implements FwStructureFormQueryRepository {
    @PersistenceContext
    EntityManager entityManager;
    @Override
    public List<ExportForBuildingVo> getAllData(String status) {
        StringBuilder sql = new StringBuilder("SELECT count( id ) number, belongto_province as belongtoProvince, structure_type as structurType from fw_structure_form");
        StringBuilder where = new StringBuilder(" where del_flag='0'");
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            where.append(" and status = :status");
        }
        where.append(" and status >0");
        where.append(" group by belongto_province,structure_type");

        Query query = entityManager.createNativeQuery(sql.append(where).toString());
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
           query.setParameter("status",status);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(ExportForBuildingVo.class));

        List<ExportForBuildingVo> resultList = query.getResultList();
        return resultList;
    }

    @Override
    public List<ExportForBuildingVo> getAllDataByProvinceName(String belongtoProvince,String status) {
        StringBuilder sql = new StringBuilder("SELECT  count( id ) number,belongto_province as belongtoProvince , structure_type as structurType from fw_structure_form");
        StringBuilder where = new StringBuilder(" where belongto_province = :belongtoProvince and del_flag='0'");
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            where.append(" and status = :status");
        }
        where.append(" and status >0");
        where.append(" group by belongto_province,structure_type");

        Query query = entityManager.createNativeQuery(sql.append(where).toString());
        query.setParameter("belongtoProvince",belongtoProvince);
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            query.setParameter("status",status);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(ExportForBuildingVo.class));

        List<ExportForBuildingVo> resultList = query.getResultList();
        return resultList;
    }

    @Override
    public List<ExportForBuildingWithTaskVo> getAllDataWithTask(String status) {
        StringBuilder sql = new StringBuilder("SELECT count( id ) number, belongto_province as belongtoProvince, structure_type as structurType, task_name taskName from fw_structure_form");
        StringBuilder where = new StringBuilder(" where del_flag='0' ");
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            where.append(" and status = :status");
        }
        where.append(" and status >0");
        where.append(" group by belongto_province,structure_type, task_name order by belongto_province,task_name");

        Query query = entityManager.createNativeQuery(sql.append(where).toString());
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            query.setParameter("status",status);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(ExportForBuildingWithTaskVo.class));
        List<ExportForBuildingWithTaskVo> resultList = query.getResultList();
        return resultList;
    }

    @Override
    public List<ExportForBuildingWithTaskVo> getAllDataWithTaskByProvinceName(String belongtoProvince,String status) {
        StringBuilder sql = new StringBuilder("SELECT  count( id ) number,belongto_province as belongtoProvince , structure_type as structurType, task_name taskName from fw_structure_form");
        StringBuilder where = new StringBuilder(" where belongto_province = :belongtoProvince and del_flag='0' ");
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            where.append(" and status = :status");
        }
        where.append(" and status >0");
        where.append(" group by belongto_province,structure_type, task_name order by belongto_province,task_name");

        Query query = entityManager.createNativeQuery(sql.append(where).toString());
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            query.setParameter("status",status);
        }
        query.setParameter("belongtoProvince",belongtoProvince);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(ExportForBuildingWithTaskVo.class));

        List<ExportForBuildingWithTaskVo> resultList = query.getResultList();
        return resultList;
    }

    @Override
    public List<ExportForBuildingAreaVo> getAllAreaData(String belongtoProvince,String status) {
        StringBuilder sql = new StringBuilder("SELECT cast(sum(building_area) AS decimal(10,2)) number, belongto_province as belongtoProvince, structure_type as structurType from fw_structure_form");
        StringBuilder where = new StringBuilder(" where del_flag='0' ");

        if(!StringUtils.isEmpty(belongtoProvince)){
            where.append(" and belongto_province = :belongtoProvince");
        }
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            where.append(" and status = :status");
        }
        where.append(" and status >0");
        where.append(" group by belongto_province,structure_type");

        Query query = entityManager.createNativeQuery(sql.append(where).toString());
        if(!StringUtils.isEmpty(belongtoProvince)) {
            query.setParameter("belongtoProvince", belongtoProvince);
        }
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            query.setParameter("status",status);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(ExportForBuildingAreaVo.class));

        List<ExportForBuildingAreaVo> resultList = query.getResultList();
        return resultList;
    }

    @Override
    public List<ExportForBuildingAreaWithTaskVo> getAllAreaDataWithTask(String belongtoProvince,String status) {
        StringBuilder sql = new StringBuilder("SELECT  cast(sum(building_area) AS decimal(10,2))  number,belongto_province as belongtoProvince , structure_type as structurType, task_name taskName from fw_structure_form");
        StringBuilder where = new StringBuilder(" where  del_flag='0' ");
        if(!StringUtils.isEmpty(belongtoProvince)){
            where.append(" and belongto_province = :belongtoProvince");
        }
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            where.append(" and status = :status");
        }
        where.append(" and status >0");
        where.append(" group by belongto_province,structure_type, task_name order by belongto_province,task_name");

        Query query = entityManager.createNativeQuery(sql.append(where).toString());
        if(!StringUtils.isEmpty(belongtoProvince)) {
            query.setParameter("belongtoProvince", belongtoProvince);
        }
        if(!StringUtils.isEmpty(status) && !"null".equals(status)){
            query.setParameter("status",status);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(ExportForBuildingAreaWithTaskVo.class));

        List<ExportForBuildingAreaWithTaskVo> resultList = query.getResultList();
        return resultList;
    }
}
